﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Matthew
{
    class DBManager
    {
        private static string connectionString;

        public static bool AddOrder(Order order)
        {
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;
            SqlTransaction myTransaction;

            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();
                myTransaction = myConn.BeginTransaction();

                strQuery = "INSERT INTO Orders (OrderID, CustomerID, OrderDate, Instructions) VALUES (@OrderID, @CustomerID, @OrderDate, @Instructions)";
                myCmd = new SqlCommand(strQuery, myConn, myTransaction);
                myCmd.Parameters.AddWithValue("@OrderID", order.OrderID);
                myCmd.Parameters.AddWithValue("@CustomerID", order.BuyerID);
                myCmd.Parameters.AddWithValue("@OrderDate", order.Date);
                myCmd.Parameters.AddWithValue("@Instructions", order.SpecialInstructions);
                myCmd.ExecuteNonQuery();

                foreach (OrderDetail detail in order.OrderDetails)
                {
                    strQuery = "INSERT INTO OrderDetail (OrderID, ProductID, Quantity, askingPrice) VALUES (@OrderID, @ProductID, @Quantity, @AskingPrice)";
                    myCmd = new SqlCommand(strQuery, myConn, myTransaction);
                    myCmd.Parameters.AddWithValue("@OrderID", order.OrderID);
                    myCmd.Parameters.AddWithValue("@ProductID", detail.ItemID);
                    myCmd.Parameters.AddWithValue("@Quantity", detail.Quantity);
                    myCmd.Parameters.AddWithValue("@AskingPrice", detail.UnitPrice);
                    myCmd.ExecuteNonQuery();
                }

                myTransaction.Commit();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static List<Order> GetAllOrders()
        {
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery;
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            List<Order> result = new List<Order>();

                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "SELECT * FROM Orders";
                myCmd = new SqlCommand(strQuery, myConn);

                SqlDataReader dr = myCmd.ExecuteReader();
                while (dr.Read())
                {
                    Order order = new Order();

                    order.OrderID = dr["OrderID"].ToString();
                    order.BuyerID = dr["CustomerID"].ToString();
                    order.Date = dr["OrderDate"].ToString();
                    order.SpecialInstructions = dr["Instructions"].ToString();

                    result.Add(order);
                }

                dr.Close();
                myConn.Close();

                foreach (Order order in result)
                {
                    myConn.Open();

                    strQuery = "SELECT * FROM OrderDetail WHERE OrderID=@OrderID";
                    myCmd = new SqlCommand(strQuery, myConn);
                    myCmd.Parameters.AddWithValue("@OrderID", order.OrderID);

                    dr = myCmd.ExecuteReader();

                    while (dr.Read())
                    {
                        OrderDetail orderDetail = new OrderDetail();

                        orderDetail.ItemID = dr["ProductID"].ToString();
                        orderDetail.Quantity = dr["Quantity"].ToString();
                        orderDetail.UnitPrice = dr["askingPrice"].ToString();

                        order.OrderDetails.Add(orderDetail);
                    }

                    myConn.Close();
                }

                return result;

        }

        public static List<ProductDetail> GetProductDetails(string orderID)
        {
            // this method is primarily used for display purposes in FrmInvoice2.cs
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery;
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            List<ProductDetail> result = new List<ProductDetail>();

            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "SELECT od.*, p.* FROM OrderDetail od JOIN Product p ON od.ProductID = p.ProductID WHERE od.OrderID = @OrderID";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@OrderID", orderID);

                SqlDataReader dr = myCmd.ExecuteReader();
                while (dr.Read())
                {
                    ProductDetail prodDetail = new ProductDetail();

                    prodDetail.ItemID = dr["ProductID"].ToString();
                    prodDetail.Quantity = dr["Quantity"].ToString();
                    prodDetail.BuyerAskingPrice = dr["askingPrice"].ToString();
                    prodDetail.Name = dr["Name"].ToString();
                    prodDetail.Manufacturer = dr["Manufacturer"].ToString();
                    prodDetail.CompanyUnitPrice = dr["UnitPrice"].ToString();

                    result.Add(prodDetail);
                }

                dr.Close();
                myConn.Close();

                return result;
            }
            catch
            {
                return null;
            }
        }

        public static string GetUnitPrice(string productID)
        {
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery;
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            string result = "";

            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "SELECT UnitPrice FROM Product WHERE ProductID=@ProductID";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@ProductID", productID);

                SqlDataReader dr = myCmd.ExecuteReader();
                while (dr.Read())
                {
                    result = dr["UnitPrice"].ToString();
                }

                dr.Close();
                return result;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                myConn.Close();
            }
        }

        public static bool AddInvoice(string invoiceID, string orderID)
        {
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "INSERT INTO Invoice (InvoiceID, OrderID, PaymentReceived, Date) VALUES (@InvoiceID, @OrderID, @PaymentReceived, @Date)";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@InvoiceID", invoiceID);
                myCmd.Parameters.AddWithValue("@OrderID", orderID);
                myCmd.Parameters.AddWithValue("@PaymentReceived", false);
                myCmd.Parameters.AddWithValue("@Date", DateTime.Now.ToShortDateString());
                myCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool CheckInvoiceID(string invoiceID)
        {
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery;
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "SELECT COUNT(*) FROM Invoice WHERE InvoiceID=@InvoiceID";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@InvoiceID", invoiceID);

                int count = (int)myCmd.ExecuteScalar();
                if (count == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                myConn.Close();
            }
        }

        public static bool CheckOrderID(string orderID)
        {
            connectionString = "data source=localhost; initial catalog=" + Settings.DBName + "; integrated security=false; user id=" + Settings.DBUsername + "; password=" + Settings.DBPassword;

            String strQuery;
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery = "SELECT COUNT(*) FROM Invoice WHERE OrderID=@OrderID";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@OrderID", orderID);

                int count = (int)myCmd.ExecuteScalar();
                if (count == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                myConn.Close();
            }
        }
    }
}
